package br.com.fiap.trabalho.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import br.com.fiap.trabalho.entity.Actor;
import br.com.fiap.trabalho.entity.Category;
import br.com.fiap.trabalho.entity.Movie;
import br.com.fiap.trabalho.entity.Studio;

public class JDBCMovieDAO implements MovieDAO {
	
	private StudioDAO studioDAO = new JDBCStudioDAO();
	private ActorDAO actorDAO = new JDBCActorDAO();
	private CategoryDAO categoryDAO = new JDBCCategoryDAO();
	
	private Map<Integer, Movie> moviesMap = new HashMap<Integer, Movie>();
	
	public Movie createMovie(Movie movie) {
		Connection connection = null;
		
		try {
			connection = JDBCConnectionManager.getConnection();
			
			connection.setAutoCommit(false);
			
			if (movie.getStudio() == null) {
				throw new IllegalArgumentException("Nao e possivel inserir filmes sem studio associado");
			}
			
			Studio presistedStudio = studioDAO.createStudio(movie.getStudio());
			movie.setStudio(presistedStudio);
			
			if (movie.getId() == null) {
				insertMovie(movie, connection);
				insertIntoMovieActor(movie, connection);
				insertIntoMovieCategory(movie, connection);
			} else {
				updateMovie(movie, connection);
			}
			
			connection.commit();
			connection.setAutoCommit(true);
			
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			movie = null;
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return movie;
	}
	
	private void insertIntoMovieCategory(Movie movie, Connection connection) throws SQLException {
		
		if (movie.getCategories() == null || movie.getCategories().isEmpty()) {
			throw new IllegalArgumentException("Nao e possivel inserir filmes sem categorias associadas");
		}
		
		PreparedStatement ps = null;
		
		List<Category> persistedCategories = new ArrayList<Category>(movie.getCategories().size());
		
		for (Category cat : movie.getCategories()) {
			
			Category persistedCategory;
			
			if (cat.getId() == null) {
				persistedCategory = categoryDAO.createCategory(cat);
				persistedCategories.add(persistedCategory);
			} else {
				persistedCategory = cat;
			}
			
			String sql = "insert into movie_category (movies_id, categories_id) values (?, ?)";
			
			ps = connection.prepareStatement(sql);
			
			ps.setInt(1, movie.getId());
			ps.setInt(2, persistedCategory.getId());
			ps.executeUpdate();
		}
		
		movie.setCategories(persistedCategories);
		
		if (ps != null) ps.close();
		
	}
	
	private void insertIntoMovieActor(Movie movie, Connection connection) throws SQLException {
		
		if (movie.getActors() == null || movie.getActors().isEmpty()) {
			throw new IllegalArgumentException("Nao e possivel inserir filmes sem atores associadas");
		}
		
		PreparedStatement ps = null;
		
		List<Actor> persistedActors = new ArrayList<Actor>(movie.getActors().size());
		
		for (Actor actor : movie.getActors()) {
			
			Actor persistedActor;
			
			if (actor.getId() == null) {
				persistedActor = actorDAO.createActor(actor);
				persistedActors.add(persistedActor);
			} else {
				persistedActor = actor;
			}
			
			String sql = "insert into movie_actor (movies_id, actors_id) values (?, ?)";
			
			ps = connection.prepareStatement(sql);
			
			ps.setInt(1, movie.getId());
			ps.setInt(2, persistedActor.getId());
			ps.executeUpdate();
		}
		
		movie.setActors(persistedActors);
		
		if (ps != null) ps.close();
		
	}

	public boolean deleteMovie(Movie movie) {
		
		Connection connection = null;
		
		boolean deleted = false;
		
		try {
			connection = JDBCConnectionManager.getConnection();
			
			String sql = "delete from movie where id = ?";
			
			PreparedStatement ps = connection.prepareStatement(sql);
			
			ps.setInt(1, movie.getId());
			ps.execute();
			
			deleted = true;
			
			ps.close();
			
		} catch (Exception e) {
			e.printStackTrace();
			
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return deleted;
		
	}

	public List<Movie> selectMovieByYear(Integer year) {
		List<Movie> movies = new ArrayList<Movie>();
		Connection connection = null;
		
		try {
			
			connection = JDBCConnectionManager.getConnection();
			
			StringBuilder sql = new StringBuilder();
			
			sql.append(" select m.id as movie_id, m.title as movie_title, m.year_movie as movie_year, s.id as studio_id, s.name as studio_name, c.id as category_id, c.name as category_name, a.id as actor_id, a.fullName as actor_fullName, a.birthDate as actor_birthDate ");
			sql.append(" from movie m ");
			sql.append(" inner join studio s on m.studio_id = s.id ");
			sql.append(" inner join movie_actor ma on m.id = ma.movies_id ");
			sql.append(" inner join actor a on ma.actors_id = a.id ");
			sql.append(" inner join movie_category mc on m.id = mc.movies_id ");
			sql.append(" inner join category c on mc.categories_id = c.id ");
			sql.append(" where m.year_movie = ? ");
			
			PreparedStatement ps = connection.prepareStatement(sql.toString());
			ps.setInt(1, year);

			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				movies.add(getResutSetMovie(rs));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
			
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return movies;
	}

	public List<Movie> selectMovieByActorName(String actorName) {
		
		List<Movie> movies = new ArrayList<Movie>();
		Connection connection = null;
		
		try {
			
			connection = JDBCConnectionManager.getConnection();

			StringBuilder sql = new StringBuilder();
			
			sql.append(" select m.id as movie_id, m.title as movie_title, m.year_movie as movie_year, s.id as studio_id, s.name as studio_name, c.id as category_id, c.name as category_name, a.id as actor_id, a.fullName as actor_fullName, a.birthDate as actor_birthDate ");
			sql.append(" from movie m ");
			sql.append(" inner join studio s on m.studio_id = s.id ");
			sql.append(" inner join movie_actor ma on m.id = ma.movies_id ");
			sql.append(" inner join actor a on ma.actors_id = a.id ");
			sql.append(" inner join movie_category mc on m.id = mc.movies_id ");
			sql.append(" inner join category c on mc.categories_id = c.id ");
			sql.append(" where a.fullName like ? ");
			
			PreparedStatement ps = connection.prepareStatement(sql.toString());
			ps.setString(1, "%"+actorName+"%");

			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				movies.add(getResutSetMovie(rs));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
			
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return movies;
		
	}

	public List<Movie> selectMovieByCategoryName(String categoryName) {
		
		List<Movie> movies = new ArrayList<Movie>();
		Connection connection = null;
		
		try {
			
			connection = JDBCConnectionManager.getConnection();
			
			StringBuilder sql = new StringBuilder();
			
			sql.append(" select m.id as movie_id, m.title as movie_title, m.year_movie as movie_year, s.id as studio_id, s.name as studio_name, c.id as category_id, c.name as category_name, a.id as actor_id, a.fullName as actor_fullName, a.birthDate as actor_birthDate ");
			sql.append(" from movie m ");
			sql.append(" inner join studio s on m.studio_id = s.id ");
			sql.append(" inner join movie_actor ma on m.id = ma.movies_id ");
			sql.append(" inner join actor a on ma.actors_id = a.id ");
			sql.append(" inner join movie_category mc on m.id = mc.movies_id ");
			sql.append(" inner join category c on mc.categories_id = c.id ");
			sql.append(" where c.name like ? ");
			
			PreparedStatement ps = connection.prepareStatement(sql.toString());
			ps.setString(1, "%"+categoryName+"%");

			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				movies.add(getResutSetMovie(rs));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
			
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return movies;
		
	}

	public List<Movie> selectMovieByStudioName(String studioName) {
		
		List<Movie> movies = new ArrayList<Movie>();
		Connection connection = null;
		
		try {
			
			connection = JDBCConnectionManager.getConnection();
			
			StringBuilder sql = new StringBuilder();
			
			sql.append(" select m.id as movie_id, m.title as movie_title, m.year_movie as movie_year, s.id as studio_id, s.name as studio_name, c.id as category_id, c.name as category_name, a.id as actor_id, a.fullName as actor_fullName, a.birthDate as actor_birthDate ");
			sql.append(" from movie m ");
			sql.append(" inner join studio s on m.studio_id = s.id ");
			sql.append(" inner join movie_actor ma on m.id = ma.movies_id ");
			sql.append(" inner join actor a on ma.actors_id = a.id ");
			sql.append(" inner join movie_category mc on m.id = mc.movies_id ");
			sql.append(" inner join category c on mc.categories_id = c.id ");
			sql.append(" where s.name = ? ");
			
			PreparedStatement ps = connection.prepareStatement(sql.toString());
			ps.setString(1, "%"+studioName+"%");

			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				movies.add(getResutSetMovie(rs));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
			
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return movies;
		
	}
	
	private void insertMovie(Movie movie, Connection connection)
			throws SQLException {
		
		String sql = "insert into movie (title, year_movie, studio_id) values (?, ?, ?)";
		
		PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		ps.setString(1, movie.getTitle());
		ps.setInt(2, movie.getYear());
		ps.setInt(3, movie.getStudio().getId());
		
		ps.executeUpdate();
		
		setIdOnEntity(movie, ps);
		
		ps.close();
	}
	
	private void updateMovie(Movie movie, Connection connection)
			throws Exception {
		
		String selectById = "select * from movie where id = ?";
		
		PreparedStatement ps = connection.prepareStatement(selectById);
		ps.setInt(1, movie.getId());
		
		ResultSet rs = ps.executeQuery();
		Movie persistedMovie = null;
		
		if (rs.next()) {
			persistedMovie = new Movie();
			
			persistedMovie.setId(rs.getInt("id"));
			persistedMovie.setTitle(rs.getString("title"));
			persistedMovie.setYear(rs.getInt("year_movie"));
		}
		
		if (persistedMovie == null) {
			throw new Exception("No Movie found with id = " + movie.getId());
		}
			
		String sql = "update movie set title = ?, year_movie = ?, studio_id = ?";
		
		ps = connection.prepareStatement(sql);
		ps.setString(1, movie.getTitle());
		ps.setInt(2, movie.getYear());
		ps.setInt(3, movie.getStudio().getId());
		
		ps.executeUpdate();
		
		ps.close();
	}
	
	private Movie getResutSetMovie(ResultSet rs) throws SQLException {
		
		Movie cachedMovie = moviesMap.get(rs.getInt("movie_id"));
		
		if (cachedMovie == null) {
			cachedMovie = new Movie();
			
			cachedMovie.setId(rs.getInt("movie_id"));
			cachedMovie.setTitle(rs.getString("movie_title"));
			cachedMovie.setYear(rs.getInt("movie_year"));
			
			try{
				rs.getInt("category_id");
			}catch(SQLException sqe){
				return cachedMovie;
			}
			
			moviesMap.put(cachedMovie.getId(), cachedMovie);
		}
		
		List<Category> categories = cachedMovie.getCategories();
		
		try{
			rs.getInt("category_id");
		}catch(SQLException sqe){
			return cachedMovie;
		}
		
		Integer idCategory = rs.getInt("category_id");
		
		if (idCategory != 0 && categories == null) {
			categories = new ArrayList<Category>();
			cachedMovie.setCategories(categories);
			
			Category category = new Category();
			category.setId(rs.getInt("category_id"));
			category.setName(rs.getString("category_name"));
			
			categories.add(category);
		}
		
		List<Actor> actors = cachedMovie.getActors();
		
		Integer idActor = rs.getInt("actor_id");
		
		if (idActor != 0 && actors == null) {
			actors = new ArrayList<Actor>();
			cachedMovie.setActors(actors);
			
			Actor actor = new Actor();
			actor.setId(rs.getInt("actor_id"));
			actor.setBirthDate(rs.getDate("actor_birthDate"));
			actor.setFullName(rs.getString("actor_fullName"));
			
			actors.add(actor);
		}
		
		Studio studio = cachedMovie.getStudio();
		
		if (studio == null) {
			studio = new Studio();
			
			studio.setId(rs.getInt("studio_id"));
			studio.setName(rs.getString("studio_name"));
			
			cachedMovie.setStudio(studio);
		}
		
		
		return cachedMovie;
		
	}
	
	private void setIdOnEntity(Movie movie, PreparedStatement ps) throws SQLException {
		
		ResultSet generatedKeys = ps.getGeneratedKeys();
		if (generatedKeys.next()) {
		    movie.setId(generatedKeys.getInt(1));
		}
		
	}
	
	public List<Movie> selectMovies(int numberOfElements) {
		
		List<Movie> movies = new ArrayList<Movie>();
		Connection connection = null;
		
		try {
			
			connection = JDBCConnectionManager.getConnection();
			
			StringBuilder sql = new StringBuilder();
			
			sql.append(" select m.id as movie_id, m.title as movie_title, m.year_movie as movie_year");
			sql.append(" from movie m ");
			
			PreparedStatement ps = connection.prepareStatement(sql.toString());

			ps.setMaxRows(numberOfElements);
			
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()) {
				movies.add(getResutSetMovie(rs));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			return null;
			
		} finally {
			if (connection != null) {
				JDBCConnectionManager.closeConnection(connection);
			}
		}
		
		return movies;
		
	}

}
